前言

SQL的查詢語法今天到最後一部分?

要練習關聯式資料庫最重要的精髓！

就是查詢出在不同資料表之間有關聯的資料

我上的課程一樣是郭耀仁老師在台大資訊系統訓練班所開設的『SQL資料分析』

這篇文章會使用老師上課教到的觀念

今天練習分成兩個部分

1.) 子查詢

2.) 垂直與水平合併

在子查詢裡可以看到在一段SQL 中包含著另外一段的 SQL

可以處理三種情形

但實作過後會發現

當要處理的資料來自不同資料表

會變得很不直觀，寫起來也容易出錯

就需要使用到垂直與水平合併 UNION跟 JOIN

來讓語法簡介快速又方便

接下來就開始練習進階的查詢語法吧～～

使用的資料庫來源分別是：

IMDb.com 最高評等的電影 https://www.imdb.com/chart/top

子查詢

如果一段 SQL 中包含著另外一段的 SQL，這樣的 SQL 結構就被稱為子查詢（Subquery）

情境一：查詢的篩選條件必須要先做一個查詢才能得知

我們想知道 imdb 資料庫的 movies 資料表中片長最短的電影是哪一部。

SELECT column_names
  FROM table_name
 WHERE conditions (Another SELECT statement);

原本會是分成兩段的查詢

SELECT MIN(runtime) AS minimum_runtime  -- 先查詢「最短」的片長是幾分鐘。
  FROM movies;

SELECT *
  FROM movies
 WHERE runtime = 45;  -- 再依據前一個查詢結果作為篩選條件。

將第一個查詢完整的放到第二個查詢內

SELECT *
  FROM movies
 WHERE runtime = (SELECT MIN(runtime) AS minimum_runtime 
                  FROM movies);

情境二：查詢的計算內容必須要先做一個查詢才能得知

我們想知道 imdb 資料庫的 movies 資料表中，在2000年之後上映的電影佔比為多少？

我們要分兩次查詢來完成

• 先查詢在2000年之後上映的電影有幾部。
• 再依據前一個查詢結果作為計算內容。

SELECT COUNT(*) AS number_of_movies
  FROM movies
 WHERE release_year >= 2000;  

SELECT 101 / CAST(COUNT(*) AS REAL) AS millennium_percentage  
  FROM movies;

合併成一個式子

SELECT (Another SELECT statement) AS alias
  FROM table_name;

SELECT (SELECT COUNT(*) AS number_of_movies
        FROM movies
        WHERE release_year >= 2000)/ CAST(COUNT(*) AS REAL) AS millennium_percentage  
  FROM movies;

也可以應用子查詢將一段 SQL 查詢結果作為資料表

SELECT column_names
  FROM (SELECT column_names FROM table_name) AS alias;

記得取別名，之後可以使用到欄位的指定

SELECT *
  FROM (SELECT release_year,
               MAX(rating) AS max_rating
          FROM movies
         GROUP BY release_year) AS max_rating_each_year
 ORDER BY release_year DESC
 LIMIT 10;

垂直合併

以 UNION 垂直合併，結合觀測值

A SELECT statement
UNION
Another SELECT statement

SELECT director AS my_favorites
  FROM movies
 WHERE director IN ('Christopher Nolan', 'Steven Spielberg')
 UNION
SELECT name
  FROM actors
 WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio');

使用 UNION 的注意事項

• 垂直合併的欄位數要相同。
• 垂直合併的 SQL 若有使用到 ORDER BY 只能放在 UNION 之後。
• 垂直合併的重複觀測值會被省略。

若希望保留重複觀測值，改使用 UNION ALL

SELECT director AS my_favorites
  FROM movies
 WHERE director IN ('Christopher Nolan', 'Steven Spielberg')
 UNION ALL
SELECT name
  FROM actors
 WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio')
 ORDER BY my_favorites;

水平合併

暸解資料表之間透過結合鍵（Join Key）水平合併。
以 JOIN 水平合併，結合變數
因為是「水平」合併，在 FROM 後的資料表被稱為「左表格」、JOIN 後的資料表被稱為「右表格」。

SELECT left_table.column_names,
       right_table.column_names
  FROM left_table
  JOIN right_table
    ON left_table.join_key = right_table.join_key;

**結合兩張資料表：**以 JOIN 水平合併 movies 與 casting

SELECT movies.title,
       movies.director,
       movies.release_year,
       casting.actor_id
  FROM movies  -- 左表格
  JOIN casting -- 右表格
    ON movies.id = casting.movie_id
WHERE movies.title = 'The Shawshank Redemption';

**結合三張資料表：**以 JOIN水平合併 movies、casting 與 actors

SELECT movies.title,
       movies.director,
       casting.ord,
       actors.name
  FROM movies  -- 左表格
  JOIN casting -- 右表格
    ON movies.id = casting.movie_id
    JOIN actors   -- 右二表格
    ON casting.actor_id = actors.id
WHERE movies.title = 'The Shawshank Redemption';

若希望保留以左表格為主的觀測值，改使用LEFT JOIN

SELECT left_table.column_names,
       right_table.column_names
  FROM table_name AS left_table
  LEFT JOIN table_name AS right_table
    ON left_table.join_key = right_table.join_key;

SELECT two_movies.title,
       two_castings.actor_id
  -- 左表格是刺激1995與阿甘正傳的電影資料
  FROM (SELECT *
          FROM movies
         WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies
  -- 右表格是刺激1995與黑暗騎士的名單資料
  LEFT JOIN (SELECT *
               FROM casting
              WHERE movie_id IN (1, 4)) AS two_castings
    ON two_movies.id = two_castings.movie_id
 ORDER BY two_movies.title;

小結

目前學過的SQL查詢語法還是要注意使用的順序

SELECT column_names     
  FROM left_table      
  JOIN right_table     
    ON left_table.join_key = right_table.join_key
 WHERE conditions       
 GROUP BY column_names  
HAVING conditions       
 UNION SELECT statement 
 ORDER BY column_names  
 LIMIT m;              

在學完這些查詢的語法過後

就可以練習用最簡短的語法

把想要篩選出來的資料選出來